import math

from django.core.paginator import Paginator
from django.db.models import Max, Min, Sum, Avg, Count
from django.shortcuts import render, HttpResponse

# Create your views here.
from App.models import *


# 增加数据
def add_crud(request):
    # 方式1
    # try:
    #     c = CRUDModel()
    #     c.name = '张三'
    #     c.age = 23
    #     c.save()  # 同步到数据库表中
    # except Exception as e:
    #     return HttpResponse(f'{e}')
    #
    # return HttpResponse('add OK!!!')

    # # 方式2
    # try:
    #     c = CRUDModel(name='王五', age=23)
    #     c.save()  # 同步到数据库表中
    # except Exception as e:
    #     return HttpResponse(f'{e}')
    #
    # return HttpResponse('add OK!!!')

    # # 方式3
    # try:
    #     CRUDModel.objects.create(name='李四',age=23)
    # except Exception as e:
    #     return HttpResponse(f'{e}')
    #
    # return HttpResponse('add OK!!!')

    # 方式4
    try:
        # 如果第一次创建 ret为True  如果存在 则为False
        ret = CRUDModel.objects.get_or_create(name='刘涛', age=23)
    except Exception as e:
        return HttpResponse(f'{e}, {ret}')

    return HttpResponse(f'add OK!!!, {ret}')
    #
    # # 添加多条数据
    # for i in range(0, 100):
    #     c = CRUDModel.objects.get_or_create(name=f'{i}', age=i * 10)
    #
    # return HttpResponse("OK")


# 删除数据
def delete_crud(request):
    try:
        c = CRUDModel.objects.filter()  # 第一条数据
        c.delete()
    except Exception as e:
        return HttpResponse(f'删除失败, {c}')
    return HttpResponse('delete OK')


# 修改数据
def update_crud(request):
    try:
        # 修改一条数据
        c = CRUDModel.objects.first()

        c.name = '刘涛'
        c.save()
    except Exception as e:
        return HttpResponse(f'FALSE, {c}')
    return HttpResponse('update OKKK')


# 查询
def select_crud(request):
    # try:
    #     c = CRUDModel.objects.get(id=6)  # 获取单条数据
    #     b = CRUDModel.objects.get(pk=6)  # 获取单条数据
    # except Exception as e:
    #     return HttpResponse("ERROR")
    # return HttpResponse(f"查询成功！！！, {c.name, c.age} \n {b.name, b.age}")

    # filter(): 过滤
    # exclude(): 排除，取反的意思
    # filter(): 详细, 类似数据库中的where语句
    c = CRUDModel.objects.filter(age__in=[100, 200, 666, 777, 888])  # in
    # exclude(): 排除，取反的意思
    c = CRUDModel.objects.exclude(age__in=[100, 200, 666, 777, 888])  # not in
    c = CRUDModel.objects.filter(age__contains='6')  # 包含, 模糊查找，类似like
    c = CRUDModel.objects.filter(name__contains='3')  # 包含, 模糊查找，类似like
    c = CRUDModel.objects.filter(name__icontains='3')  # 包含, 模糊查找，类似like
    c = CRUDModel.objects.filter(name__regex='^wu')  # 正则匹配，姓武的
    c = CRUDModel.objects.filter(name__iregex='^wu')  # 正则匹配，忽略大小写
    # c = CRUDModel.objects.filter(age__range=[200, 400])  # 200-400之间，两边都包含

    c = CRUDModel.objects.filter(name__startswith='wu')  # 以wu开头，忽略大小写
    c = CRUDModel.objects.filter(name__istartswith='wu')  # 以wu开头，忽略大小写
    c = CRUDModel.objects.filter(name__endswith='wu')  # 以wu结尾，忽略大小写
    c = CRUDModel.objects.filter(name__iendswith='wu')  # 以wu结尾，忽略大小写
    print(c)

    # 聚合函数：max,min,sum
    result = CRUDModel.objects.aggregate(Max('age'))  # 最大值  {'age__max': 666}
    result = CRUDModel.objects.aggregate(Min('age'))  # 最小值  {'age__min': 100}
    result = CRUDModel.objects.aggregate(Sum('age'))  # 求和  {'age__sum': 1666}
    result = CRUDModel.objects.aggregate(Avg('age'))  # 平均值  {'age__avg': 333.2}
    result = CRUDModel.objects.aggregate(Count('age'))  # 计数  {'age__count': 5}
    print(result)

    # 排序
    c = CRUDModel.objects.all().order_by('age')  # 升序
    c = CRUDModel.objects.all().order_by('age', '-id')  # 先按照age升序，如果age相同则按id降序排列
    c = CRUDModel.objects.all().order_by('-age')  # 降序
    print(c)
    return HttpResponse(f"查询成功！！！")


def paginate(request, page=1):
    # 页码：page
    # 每页数量：per_page
    per_page = 10

    # 分页功能：
    #  数据 =【1,2,3,4,5,...,100】
    #   第几页       数据范围       数据下标范围      切片
    #   page=1        1 ~ 10      0 ~ 9        [0 : 10]
    #   page=2       11 ~ 20     10 ~ 19       [10 : 20]
    #   page=3       21 ~ 30     20 ~ 29       [20 : 30]
    #   page=4       31 ~ 40     30 ~ 39       [30 : 40]
    #   ...
    #   page=n                        [(n-1) * 10  :  n * 10]
    #   page=page                     [(page-1) * per_page  :  page * per_page]

    # 实现分页功能
    c = CRUDModel.objects.all()
    c = c[(page - 1) * per_page:  page * per_page]

    # 总页数
    total = CRUDModel.objects.count()  # 总数据量
    total_page = math.ceil(total / per_page)  # 总页数
    pages = range(1, total_page + 1)  # 1,2,3,4,5,6,7...

    data = {'c': c, 'c': pages}
    return render(request, 'paginate.html', {'c': c, 'pages': pages, })


# 分页器：自动分页
def paginate2(request, page=1):
    per_page = 10

    all_data = CRUDModel.objects.all()

    # 分页器
    paginator = Paginator(all_data, per_page)
    c = paginator.page(page)  # 获取第page页的数据
    pages = paginator.page_range  # 页码范围，可以遍历
    return render(request, 'paginate2.html', {'c': c,
                                              'pages': pages})
